#!/Library/Frameworks/Python.framework/Versions/3.7/bin/python3.7
# -*- coding: utf-8 -*- 例如，可添加# -*- coding: utf-8 -*-
import uuid
from decimal import Decimal

from openpyxl import load_workbook
import pymysql.cursors

# 连接数据库
connect = pymysql.Connect(host='10.40.3.158',port=33306,user='root',passwd='2018@Juyou',db='uat_maintain_copy',charset='utf8')
# 获取游标
cursor = connect.cursor()

table_name = "/Users/not_every/AllProjects/gitee/awesome-webapp/www/huiding/maintain/资产信息-大东分局"
wb = load_workbook('%s.xlsx' % (table_name))

# 根据合同名称查询合同ID
cursor.execute("select id, contract_code from mt_project_contract_info where contract_name like '%指挥调度终端设备%' and create_by='6894ff1c-0d13-11eb-ba0e-0242ac110002'")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
contract_id = data[0]
code = data[1]

for row in wb:
    num = 0
    for cell in row:
        num = num+1
        if num > 3:
            break
        # print(code)
        device_code = code+str(num).zfill(3)
        # print(device_code)

        device_type = ""
        cell2 = str(cell[2].value)
        if '硬件服务-机房-空调' == cell2:
            device_type = '["1","10","11"]'
        elif '硬件服务-机房-UPS' == cell2:
            device_type = '["1","10","12"]'
        elif '硬件服务-机房-配电' == cell2:
            device_type = '["1","10","13"]'
        elif '硬件服务-机房-监控' == cell2:
            device_type = '["1","10","14"]'
        elif '硬件服务-机房-综合布线' == cell2:
            device_type = '["1","10","15"]'
        elif '硬件服务-机房-消防' == cell2:
            device_type = '["1","10","16"]'
        elif '硬件服务-机房-其他' == cell2:
            device_type = '["1","10","17"]'
        elif '硬件服务-网络设备' == cell2:
            device_type = '["1","20"]'
        elif '硬件服务-服务器设备' == cell2:
            device_type = '["1","30"]'
        elif '硬件服务-存储设备' == cell2:
            device_type = '["1","40"]'
        elif '硬件服务-安全设备' == cell2:
            device_type = '["1","50"]'
        elif '硬件服务-网络摄像机' == cell2:
            device_type = '["1","60"]'
        elif '硬件服务-大屏幕显示设备' == cell2:
            device_type = '["1","70"]'
        elif '硬件服务-其他硬件' == cell2:
            device_type = '["1","80"]'
        elif '软件服务-专用软件' == cell2:
            device_type = '["200","210"]'
        elif '软件服务-通用软件-网站' == cell2:
            device_type = '["200","220","221"]'
        elif '软件服务-通用软件-财务软件' == cell2:
            device_type = '["200","220","222"]'
        elif '软件服务-通用软件-数据库' == cell2:
            device_type = '["200","220","223"]'
        elif '软件服务-通用软件-操作系统' == cell2:
            device_type = '["200","220","224"]'
        elif '软件服务-通用软件-中间件' == cell2:
            device_type = '["200","220","225"]'
        elif '软件服务-通用软件-杀毒软件' == cell2:
            device_type = '["200","220","226"]'
        elif '软件服务-通用软件-其他' == cell2:
            device_type = '["200","220","227"]'
        elif '硬件服务-机房-空调' == cell2:
            device_type = '["1","10","11"]'
        else:
            device_type = ''

        status = ""
        cell9 = str(cell[9].value)
        if '正常运行' == cell9:
            status = '1'
        elif '未运行' == cell9:
            status = '2'
        elif '故障中' == cell9:
            status = '3'

        buy_type = ""
        cell13 = str(cell[13].value)
        if '单一来源' == cell13:
            buy_type = '1'
        elif '公开招标' == cell13:
            buy_type = '2'

        service_method = ""
        cell15 = str(cell[15].value)
        if '现场' == cell15:
            service_method = '1'
        elif '远程' == cell15:
            service_method = '2'
        elif '现场和远程' == cell15:
            service_method = '3'

        cell11=''
        if(None!=cell[11].value):
            cell11=str(Decimal(str(cell[11].value)).quantize(Decimal('0.0000')))

        uid = uuid.uuid4()
        device_sql = "INSERT INTO mt_info_device (id, device_name, device_code, device_type, device_model, purchase_time, purchase_price, guarantee_time, manufacturer, contactor, status, service_content, service_pre_amount, apply_batch, buy_type, contract_id, create_by, create_date, update_by, update_date, del_flag) " \
                                        "VALUES ('"+str(uid)+"', '"+str(cell[1].value)+"', '"+device_code+"', '"+device_type+"', '"+str(cell[3].value)+"', '"+str(cell[4].value)+"', '"+str(cell[5].value)+"', '"+str(cell[6].value)+"', '"+str(cell[7].value)+"', '"+str(cell[8].value)+"', '"+status+"', '"+str(cell[10].value)+"', '"+cell11+"', '"+str(cell[12].value)+"', '"+buy_type+"', '"+contract_id+"', '5475e07ee1f046009e5ae7012ff190a7', now(), '5475e07ee1f046009e5ae7012ff190a7', now(), '0');"
        print(device_sql)

        device_sub_sql = "INSERT INTO mt_info_device_sub (id, device_id, service_time, service_method, service_result, target_index, target_desc, target_require, create_by, create_date, update_by, update_date, del_flag) " \
                         "VALUES (uuid() , '"+str(uid)+"', '"+str(cell[14].value)+"', '"+service_method+"', '"+str(cell[16].value)+"', '"+str(cell[17].value)+"', '"+str(cell[18].value)+"', '"+str(cell[19].value)+"', '5475e07ee1f046009e5ae7012ff190a7', now(), '5475e07ee1f046009e5ae7012ff190a7', now(), '0');"
        print(device_sub_sql)

        # print(str(cell[5].value))
        # if(None!=cell[11].value):
        #     print(Decimal(str(cell[11].value)).quantize(Decimal('0.0000')))
        # else:
        #     print('')
        # print(device_type)


cursor.close()
connect.close()
print("over...")
